"""
采用 openpyxl 操作 Excel 
"""
import os
import sys
import time
from collections import namedtuple
import logging

import openpyxl
import pyautogui as auto
from openpyxl import load_workbook
import collections

from ..config import ExcelConfig

# logging.basicConfig(filename='debug.log', format='%(asctime)s %(message)s', level=logging.DEBUG)
logger = logging.getLogger(__name__)


def is_number(value):
    try:
        v = float(value)
        return True
    except:
        pass
    return False


def tryfloat(v):
    try:
        v = float(v)
        return v
    except:
        print(f"tryfloat error: {v}")
    return 0

def tryint(v):
    try:
        v = int(v)
        return v 
    except:
        print(f'tryint error: {v}')
    return 0


SaleModel = namedtuple(
    "SaleMode",
    [
        "date",
        "sale_nr",
        "sale_mode",
        "client_id",
        "client_name",
        "variety",
        "number",
        "amount",
        "category",
    ],
)
FreeOrgModel = namedtuple(
    "FreeOrgModel",
    [
        "sheetname",
        "start_row",
        "end_row",
        "start_col",
        "end_col",
        "client_row",
        "code_col",
    ],
)
FreeTempModel = namedtuple(
    "FreeTempModel", ["sheetname", "code_col", "start_row", "end_row", "fill_col"]
)


class TempRecord:
    def __init__(
        self,
        c1=None,
        c2=None,
        c3=None,
        c4=None,
        c5=None,
        c6=None,
        c7=None,
        c8=None,
        c9=None,
        c10=None,
        c11=None,
        c12=None,
        c13=None,
        c14=None, 
        c15=None, 
        c16=None
    ):
        self.c1 = c1
        self.c2 = c2
        self.c3 = c3
        self.c4 = c4
        self.c5 = c5
        self.c6 = c6
        self.c7 = c7
        self.c8 = c8
        self.c9 = c9
        self.c10 = c10
        self.c11 = c11
        self.c12 = c12
        self.c13 = c13
        self.c14 = c14
        self.c15 = c15
        self.c16 = c16


CurrentDir = os.path.dirname(__file__)


class ExcelAccount:
    """做账专用"""

    def __init__(self, org_path=None, dest_path=None):
        self.org_workbook = load_workbook(org_path, data_only=True)
        self.dest_workbook = load_workbook(dest_path)
        self.dest_path = dest_path

    def config(
        self,
        xs_config: ExcelConfig = None,
        xt_config: ExcelConfig = None,
        dest_config: ExcelConfig = None,
    ):
        self.xs_config = xs_config
        self.xt_config = xt_config
        self.dest_config = dest_config

        self.xs_sheet = None
        if xs_config is not None:
            self.xs_sheet = self.org_workbook[xs_config.sheet_name]
        self.xt_sheet = None 
        if xt_config is not None:
            self.xt_sheet = self.org_workbook[xt_config.sheet_name]

        self.dest_sheet = self.dest_workbook[dest_config.sheet_name]

    def fill(self):
        categories = self.get_categories()
        i = self.dest_config.start_row
        for key, cates in categories.items():
            clients = {}
            for c in cates:
                k = None
                for id, cid, cname, fullname in ExcelConfig.Clients:
                    if cid == c.client_id:
                        k = (id, cid, cname, fullname)
                        break
                if k is None:
                    continue
                if clients.get(k) == None:
                    clients[k] = [c]
                else:
                    clients[k].append(c)
            for client, values in sorted(clients.items()):
                total_amount = 0
                total_number = 0
                for m in values:
                    if (
                        m.amount is None
                        or m.amount == "码洋"
                        or (m.amount == 0 and m.number == 0)
                    ):
                        continue
                    if m.number is None or m.number == "数量":
                        continue
                    total_amount += float(m.amount)
                    total_number += int(m.number)
                if total_amount == 0:
                    continue
                # print(i, total_amount)
                self.dest_sheet.cell(
                    row=i, column=self.dest_config.amount
                ).value = total_amount
                self.dest_sheet.cell(
                    row=i, column=self.dest_config.number
                ).value = total_number
                self.dest_sheet.cell(
                    row=i, column=self.dest_config.client_name
                ).value = client[2]
                self.dest_sheet.cell(
                    row=i, column=self.dest_config.category
                ).value = key
                i += 1
            i += 1
        self.dest_workbook.save(
            os.path.join(os.path.dirname(self.dest_path), "out-账目.xlsx")
        )
        print("fill account OK!")

    def get_values(self, sheet, start_row, end_row):
        values = []
        if sheet is None: return values

        for v in list(sheet.values)[start_row - 1 : end_row]:
            m = SaleModel(*v)
            values.append(m)
        return values

    def get_categories(self):
        xs_values = []
        if self.xs_config is not None:
            xs_values = self.get_values(
                self.xs_sheet, self.xs_config.start_row, self.xs_config.end_row
            )

        xt_values = []
        if self.xt_config is not None:
            xt_values = self.get_values(
                self.xt_sheet, self.xt_config.start_row, self.xt_config.end_row
            )

        categories = {}
        for m in xs_values:
            if m.category is None or m.category == "类别":
                continue
            if categories.get(m.category) == None:
                categories[m.category] = [m]
            else:
                categories[m.category].append(m)
        for m in xt_values:
            if m.category is None or m.category == "类别":
                continue
            temp = SaleModel(
                m.date,
                m.sale_nr,
                m.sale_mode,
                m.client_id,
                m.client_name,
                m.variety,
                -m.number,
                -m.amount,
                m.category,
            )
            if categories.get(m.category) == None:
                categories[m.category] = [temp]
            else:
                categories[m.category].append(temp)
        return categories


class ExcelSale:
    """
    生成销售确认函
    """

    def __init__(self):
        self.workbook = None

    def open(self, filename):
        if self.workbook is None:
            self.workbook = load_workbook(filename, data_only=True)

    def save(self, filename):
        if self.workbook is not None:
            self.workbook.save(filename)

    def get_modes(self, sheetname):

        modes = []
        sheet = self.workbook[sheetname]
        for row in sheet.values:
            # print(*row)
            m = SaleModel(*row)
            if m.client_id is None or not isinstance(m.client_id, int):
                continue
            model = SaleModel(
                m.date,
                m.sale_nr,
                m.sale_mode,
                ExcelConfig.SelectId(m.client_id),
                ExcelConfig.ClientInfos()[m.client_id][3],
                m.variety,
                m.number,
                m.amount,
                m.category,
            )
            modes.append(model)
            # print(model.client_name)
        return modes

    def fill_category(
        self,
        x_sheetname,
        x_category_col,
        org_sheetname,
        org_client_row,
        org_date_col,
        org_category_col,
        org_start_row,
        org_end_row,
        org_start_col,
        org_end_col,
        is_xt=False,
    ):
        """
        根据原始表填充销售、销退类别
            x_sheetname： 系统销售或销退表名
            org_sheetname: 原始销售表名
            is_xt: 是销退
        """
        if org_sheetname == '' or org_sheetname is None: 
            print('原始表名不能为空')
            return
        if x_sheetname == '' or x_sheetname is None:
            print('销售销退表名不能为空')
            return

        org_sheet = self.workbook[org_sheetname]
        x_sheet = self.workbook[x_sheetname]
        x_modes = self.get_modes(x_sheetname)

        for r in range(org_start_row, org_end_row + 1):
            for c in range(org_start_col, org_end_col + 1):
                client_id = org_sheet.cell(row=org_client_row, column=c).value
                if not client_id or not R.IsNumber(client_id):
                    continue
                i = 2
                for m in x_modes:
                    i = i + 1
                    if m.client_id == client_id:
                        date = org_sheet.cell(row=r, column=org_date_col).value
                        amount = org_sheet.cell(row=r, column=c).value
                        if not amount or not date:
                            continue
                        if is_xt:
                            amount = -amount
                        category = org_sheet.cell(row=r, column=org_category_col).value
                        if m.date == date and m.amount == amount:
                            # print(i,m)
                            x_sheet.cell(row=i, column=x_category_col).value = category

    def gen_sales(
        self,
        xs_sheetname,
        xt_sheetname,
        tempale_filename,
        t_sheetname="T",
        t_start_row=6,
        t_date_col=-1,
        t_salenr_col=1,
        t_variety_col=-1,
        t_number_col=3,
        t_category_col=2,
        t_amount_col=4,
        changed_catgory=None,
    ):
        ''' 生成对账清单 '''
        t_workbook = load_workbook(tempale_filename)
        outdir = os.path.join(os.path.dirname(tempale_filename), "out-对账清单")
        if not os.path.exists(outdir):
            os.mkdir(outdir)
        clients = self.get_clients(xs_sheetname, xt_sheetname)
        sheet = t_workbook[t_sheetname]

        t_discount_col = t_amount_col + 1   #添加折扣列,依赖码洋列

        for key, values in clients.items():
            cates = {}
            for m in values:
                if not m.category:
                    continue
                if cates.get(m.category) == None:
                    cates[m.category] = [m]
                else:
                    cates[m.category].append(m)
            i = 0
            keys = sorted(cates)
            sorted_values = [cates[k] for k in keys]
            for models in sorted_values:
                for m in models:
                    if not m.category or m.amount == 0:
                        continue
                    # sheet.cell(row=t_start_row + i, column=t_date_col).value = m.date
                    sheet.cell(
                        row=t_start_row + i, column=t_salenr_col
                    ).value = m.sale_nr
                    # sheet.cell(
                    #     row=t_start_row + i, column=t_variety_col
                    # ).value = m.variety  # 品种
                    sheet.cell(
                        row=t_start_row + i, column=t_number_col
                    ).value = m.number
                    sheet.cell(row=t_start_row + i, column=t_category_col).value = (
                        changed_catgory if changed_catgory else m.category
                    )  # 类别 == 对账品种
                    sheet.cell(
                        row=t_start_row + i, column=t_amount_col
                    ).value = m.amount
                    if is_number(m.sale_mode):
                        sheet.cell(row=t_start_row+i, column=t_discount_col).value = float(m.sale_mode)
                    i = i + 1
                i = i + 1 
            sheet.cell(row=2, column=1).value = key + ': '
            t_workbook.save(os.path.join(outdir, key + ".xlsx"))
            t_workbook = load_workbook(tempale_filename)
            sheet = t_workbook[t_sheetname]

    def gen_sales2(
        self,
        xs_sheetname,
        xt_sheetname,
        tempale_filename,
        t_sheetname="T",
        t_start_row=6,
        t_date_col=2,
        t_salenr_col=3,
        t_variety_col=4,
        t_number_col=5,
        t_category_col=6,
        t_amount_col=7,
        changed_catgory=None,
    ):
        ''' 生成确认函已作废 '''
        t_workbook = load_workbook(tempale_filename)
        outdir = os.path.join(os.path.dirname(tempale_filename), "out-对账清单")
        if not os.path.exists(outdir):
            os.mkdir(outdir)
        clients = self.get_clients(xs_sheetname, xt_sheetname)
        sheet = t_workbook[t_sheetname]

        t_discount_col = t_amount_col + 1   #添加折扣列,依赖码洋列

        for key, values in clients.items():
            cates = {}
            for m in values:
                if not m.category:
                    continue
                if cates.get(m.category) == None:
                    cates[m.category] = [m]
                else:
                    cates[m.category].append(m)
            i = 0
            keys = sorted(cates)
            sorted_values = [cates[k] for k in keys]
            for models in sorted_values:
                for m in models:
                    if not m.category or m.amount == 0:
                        continue
                    sheet.cell(row=t_start_row + i, column=t_date_col).value = m.date
                    sheet.cell(
                        row=t_start_row + i, column=t_salenr_col
                    ).value = m.sale_nr
                    sheet.cell(
                        row=t_start_row + i, column=t_variety_col
                    ).value = m.variety
                    sheet.cell(
                        row=t_start_row + i, column=t_number_col
                    ).value = m.number
                    sheet.cell(row=t_start_row + i, column=t_category_col).value = (
                        changed_catgory if changed_catgory else m.category
                    )
                    sheet.cell(
                        row=t_start_row + i, column=t_amount_col
                    ).value = m.amount
                    if is_number(m.sale_mode):
                        sheet.cell(row=t_start_row+i, column=t_discount_col).value = float(m.sale_mode)
                    i = i + 1
                i = i + 1
            sheet.cell(row=3, column=1).value = "单位：" + key
            t_workbook.save(os.path.join(outdir, key + ".xlsx"))
            t_workbook = load_workbook(tempale_filename)
            sheet = t_workbook[t_sheetname]


    def get_clients(self, xs_sheetname, xt_sheetname):
        xs_modes = self.get_modes(xs_sheetname)
        xt_modes = self.get_modes(xt_sheetname)
        clients = {}
        for m in xs_modes:
            if m.client_name is None or m.client_name == "客户名称":
                continue
            if clients.get(m.client_name) == None:
                clients[m.client_name] = [m]
            else:
                clients[m.client_name].append(m)
        for m in xt_modes:
            if m.client_name is None or m.client_name == "客户名称":
                continue
            temp = SaleModel(
                m.date,
                m.sale_nr,
                m.sale_mode,
                m.client_id,
                m.client_name,
                m.variety,
                -m.number,
                -m.amount,
                m.category,
            )
            if clients.get(m.client_name) == None:
                clients[m.client_name] = [temp]
            else:
                clients[m.client_name].append(temp)
        return clients


class ExcelFree(ExcelSale):
    """
    生成免费申报表
    """

    def __init__(self, template_filename):
        super().__init__()
        self.template_filename = template_filename
        self.template_workbook = load_workbook(template_filename)

    def init_models(
        self,
        org_sheet1_name="",
        org_sheet1_start_row=0,
        org_sheet1_end_row=0,
        org_sheet1_start_col=0,
        org_sheet1_end_col=0,
        org_sheet1_client_row=0,
        org_sheet1_code_col=0,
        org_sheet2_name="",
        org_sheet2_start_row=0,
        org_sheet2_end_row=0,
        org_sheet2_start_col=0,
        org_sheet2_end_col=0,
        org_sheet2_client_row=0,
        org_sheet2_code_col=0,
        temp_sheet0_name="",
        temp_sheet0_client_row=0,
        temp_sheet0_client_col=0,
        temp_sheet1_name="",
        tmep_sheet1_code_col=0,
        temp_sheet1_start_row=0,
        temp_sheet1_end_row=0,
        temp_sheet1_fill_col=0,
        temp_sheet2_name="",
        tmep_sheet2_code_col=0,
        temp_sheet2_start_row=0,
        temp_sheet2_end_row=0,
        temp_sheet2_fill_col=0,
        temp_sheet3_name="",
        tmep_sheet3_code_col=0,
        temp_sheet3_start_row=0,
        temp_sheet3_end_row=0,
        temp_sheet3_fill_col=0,
        temp_sheet4_name="",
        tmep_sheet4_code_col=0,
        temp_sheet4_start_row=0,
        temp_sheet4_end_row=0,
        temp_sheet4_fill_col=0,
        temp_sheet5_name="",
        tmep_sheet5_code_col=0,
        temp_sheet5_start_row=0,
        temp_sheet5_end_row=0,
        temp_sheet5_fill_col=0,
        temp_sheet6_name="",
        tmep_sheet6_code_col=0,
        temp_sheet6_start_row=0,
        temp_sheet6_end_row=0,
        temp_sheet6_fill_col=0,
    ):
        self.org_sheet1_model = FreeOrgModel(
            org_sheet1_name,
            org_sheet1_start_row,
            org_sheet1_end_row,
            org_sheet1_start_col,
            org_sheet1_end_col,
            org_sheet1_client_row,
            org_sheet1_code_col,
        )
        self.org_sheet2_model = FreeOrgModel(
            org_sheet2_name,
            org_sheet2_start_row,
            org_sheet2_end_row,
            org_sheet2_start_col,
            org_sheet2_end_col,
            org_sheet2_client_row,
            org_sheet2_code_col,
        )

        self.temp_sheet0_name = temp_sheet0_name
        self.temp_sheet0_client_row = temp_sheet0_client_row
        self.temp_sheet0_client_col = temp_sheet0_client_col

        self.temp_sheet1_model = FreeTempModel(
            temp_sheet1_name,
            tmep_sheet1_code_col,
            temp_sheet1_start_row,
            temp_sheet1_end_row,
            temp_sheet1_fill_col,
        )
        self.temp_sheet2_model = FreeTempModel(
            temp_sheet2_name,
            tmep_sheet2_code_col,
            temp_sheet2_start_row,
            temp_sheet2_end_row,
            temp_sheet2_fill_col,
        )
        self.temp_sheet3_model = FreeTempModel(
            temp_sheet3_name,
            tmep_sheet3_code_col,
            temp_sheet3_start_row,
            temp_sheet3_end_row,
            temp_sheet3_fill_col,
        )
        self.temp_sheet4_model = FreeTempModel(
            temp_sheet4_name,
            tmep_sheet4_code_col,
            temp_sheet4_start_row,
            temp_sheet4_end_row,
            temp_sheet4_fill_col,
        )
        self.temp_sheet5_model = FreeTempModel(
            temp_sheet5_name,
            tmep_sheet5_code_col,
            temp_sheet5_start_row,
            temp_sheet5_end_row,
            temp_sheet5_fill_col,
        )
        self.temp_sheet6_model = FreeTempModel(
            temp_sheet6_name,
            tmep_sheet6_code_col,
            temp_sheet6_start_row,
            temp_sheet6_end_row,
            temp_sheet6_fill_col,
        )

    def generate_free(self):
        if self.workbook is None:
            print("org is None")
            return
        if self.template_workbook is None:
            print("templae is None")
            return

        org_sheet1 = (
            self.workbook[self.org_sheet1_model.sheetname]
            if self.org_sheet1_model.sheetname
            else None
        )
        org_sheet2 = (
            self.workbook[self.org_sheet2_model.sheetname]
            if self.org_sheet2_model.sheetname
            else None
        )

        temp_sheet0 = (
            self.template_workbook[self.temp_sheet0_name]
            if self.temp_sheet0_name
            else None
        )
        temp_sheet1 = (
            self.template_workbook[self.temp_sheet1_model.sheetname]
            if self.temp_sheet1_model.sheetname
            else None
        )
        temp_sheet2 = (
            self.template_workbook[self.temp_sheet2_model.sheetname]
            if self.temp_sheet2_model.sheetname
            else None
        )
        temp_sheet3 = (
            self.template_workbook[self.temp_sheet3_model.sheetname]
            if self.temp_sheet3_model.sheetname
            else None
        )
        temp_sheet4 = (
            self.template_workbook[self.temp_sheet4_model.sheetname]
            if self.temp_sheet4_model.sheetname
            else None
        )
        temp_sheet5 = (
            self.template_workbook[self.temp_sheet5_model.sheetname]
            if self.temp_sheet5_model.sheetname
            else None
        )
        temp_sheet6 = (
            self.template_workbook[self.temp_sheet6_model.sheetname]
            if self.temp_sheet6_model.sheetname
            else None
        )

        outdir = os.path.join(os.path.dirname(self.template_filename), "out-免费申报")
        if not os.path.exists(outdir):
            os.mkdir(outdir)

        for col in range(
            self.org_sheet1_model.start_col, self.org_sheet1_model.end_col + 1
        ):
            if org_sheet1 is None:
                break
            client_name = org_sheet1.cell(
                row=self.org_sheet1_model.client_row, column=col
            ).value
            self.fill_sheet(
                org_sheet1,
                self.org_sheet1_model.start_row,
                self.org_sheet1_model.end_row,
                self.org_sheet1_model.code_col,
                col,
                temp_sheet1,
                self.temp_sheet1_model.start_row,
                self.temp_sheet1_model.end_row,
                self.temp_sheet1_model.code_col,
                self.temp_sheet1_model.fill_col,
            )
            self.fill_sheet(
                org_sheet1,
                self.org_sheet1_model.start_row,
                self.org_sheet1_model.end_row,
                self.org_sheet1_model.code_col,
                col,
                temp_sheet2,
                self.temp_sheet2_model.start_row,
                self.temp_sheet2_model.end_row,
                self.temp_sheet2_model.code_col,
                self.temp_sheet2_model.fill_col,
            )
            self.fill_sheet(
                org_sheet1,
                self.org_sheet1_model.start_row,
                self.org_sheet1_model.end_row,
                self.org_sheet1_model.code_col,
                col,
                temp_sheet3,
                self.temp_sheet3_model.start_row,
                self.temp_sheet3_model.end_row,
                self.temp_sheet3_model.code_col,
                self.temp_sheet3_model.fill_col,
            )
            self.fill_sheet(
                org_sheet1,
                self.org_sheet1_model.start_row,
                self.org_sheet1_model.end_row,
                self.org_sheet1_model.code_col,
                col,
                temp_sheet4,
                self.temp_sheet4_model.start_row,
                self.temp_sheet4_model.end_row,
                self.temp_sheet4_model.code_col,
                self.temp_sheet4_model.fill_col,
            )
            self.fill_sheet(
                org_sheet1,
                self.org_sheet1_model.start_row,
                self.org_sheet1_model.end_row,
                self.org_sheet1_model.code_col,
                col,
                temp_sheet5,
                self.temp_sheet5_model.start_row,
                self.temp_sheet5_model.end_row,
                self.temp_sheet5_model.code_col,
                self.temp_sheet5_model.fill_col,
            )

            self.fill_sheet(
                org_sheet1,
                self.org_sheet1_model.start_row,
                self.org_sheet1_model.end_row,
                self.org_sheet1_model.code_col,
                col,
                temp_sheet6,
                self.temp_sheet6_model.start_row,
                self.temp_sheet6_model.end_row,
                self.temp_sheet6_model.code_col,
                self.temp_sheet6_model.fill_col,
            )

            if temp_sheet0:
                temp_sheet0.cell(
                    row=self.temp_sheet0_client_row, column=self.temp_sheet0_client_col
                ).value = (
                    "填报单位：" + client_name
                )  #'单位名称： ' + client_name
            self.template_workbook.save(os.path.join(outdir, client_name + ".xlsx"))
            self.template_workbook = load_workbook(self.template_filename)
            temp_sheet0 = (
                self.template_workbook[self.temp_sheet0_name]
                if self.temp_sheet0_name
                else None
            )
            temp_sheet1 = (
                self.template_workbook[self.temp_sheet1_model.sheetname]
                if self.temp_sheet1_model.sheetname
                else None
            )
            temp_sheet2 = (
                self.template_workbook[self.temp_sheet2_model.sheetname]
                if self.temp_sheet2_model.sheetname
                else None
            )
            temp_sheet3 = (
                self.template_workbook[self.temp_sheet3_model.sheetname]
                if self.temp_sheet3_model.sheetname
                else None
            )
            temp_sheet4 = (
                self.template_workbook[self.temp_sheet4_model.sheetname]
                if self.temp_sheet4_model.sheetname
                else None
            )
            temp_sheet5 = (
                self.template_workbook[self.temp_sheet5_model.sheetname]
                if self.temp_sheet5_model.sheetname
                else None
            )
            temp_sheet6 = (
                self.template_workbook[self.temp_sheet6_model.sheetname]
                if self.temp_sheet6_model.sheetname
                else None
            )

        for col in range(
            self.org_sheet2_model.start_col, self.org_sheet2_model.end_col + 1
        ):
            if org_sheet2 is None:
                break
            client_name = org_sheet2.cell(
                row=self.org_sheet2_model.client_row, column=col
            ).value
            self.fill_sheet(
                org_sheet2,
                self.org_sheet2_model.start_row,
                self.org_sheet2_model.end_row,
                self.org_sheet2_model.code_col,
                col,
                temp_sheet1,
                self.temp_sheet1_model.start_row,
                self.temp_sheet1_model.end_row,
                self.temp_sheet1_model.code_col,
                self.temp_sheet1_model.fill_col,
            )
            self.fill_sheet(
                org_sheet2,
                self.org_sheet2_model.start_row,
                self.org_sheet2_model.end_row,
                self.org_sheet2_model.code_col,
                col,
                temp_sheet2,
                self.temp_sheet2_model.start_row,
                self.temp_sheet2_model.end_row,
                self.temp_sheet2_model.code_col,
                self.temp_sheet2_model.fill_col,
            )
            self.fill_sheet(
                org_sheet2,
                self.org_sheet2_model.start_row,
                self.org_sheet2_model.end_row,
                self.org_sheet2_model.code_col,
                col,
                temp_sheet3,
                self.temp_sheet3_model.start_row,
                self.temp_sheet3_model.end_row,
                self.temp_sheet3_model.code_col,
                self.temp_sheet3_model.fill_col,
            )
            self.fill_sheet(
                org_sheet2,
                self.org_sheet2_model.start_row,
                self.org_sheet2_model.end_row,
                self.org_sheet2_model.code_col,
                col,
                temp_sheet4,
                self.temp_sheet4_model.start_row,
                self.temp_sheet4_model.end_row,
                self.temp_sheet4_model.code_col,
                self.temp_sheet4_model.fill_col,
            )
            self.fill_sheet(
                org_sheet2,
                self.org_sheet2_model.start_row,
                self.org_sheet2_model.end_row,
                self.org_sheet2_model.code_col,
                col,
                temp_sheet5,
                self.temp_sheet5_model.start_row,
                self.temp_sheet5_model.end_row,
                self.temp_sheet5_model.code_col,
                self.temp_sheet5_model.fill_col,
            )
            self.fill_sheet(
                org_sheet2,
                self.org_sheet2_model.start_row,
                self.org_sheet2_model.end_row,
                self.org_sheet2_model.code_col,
                col,
                temp_sheet6,
                self.temp_sheet6_model.start_row,
                self.temp_sheet6_model.end_row,
                self.temp_sheet6_model.code_col,
                self.temp_sheet6_model.fill_col,
            )
            if temp_sheet0:
                temp_sheet0.cell(
                    row=self.temp_sheet0_client_row, column=self.temp_sheet0_client_col
                ).value = (
                    "填报单位：" + client_name
                )  #'单位名称:' + client_name
            self.template_workbook.save(os.path.join(outdir, client_name + ".xlsx"))
            self.template_workbook = load_workbook(self.template_filename)
            temp_sheet0 = (
                self.template_workbook[self.temp_sheet0_name]
                if self.temp_sheet0_name
                else None
            )
            temp_sheet1 = (
                self.template_workbook[self.temp_sheet1_model.sheetname]
                if self.temp_sheet1_model.sheetname
                else None
            )
            temp_sheet2 = (
                self.template_workbook[self.temp_sheet2_model.sheetname]
                if self.temp_sheet2_model.sheetname
                else None
            )
            temp_sheet3 = (
                self.template_workbook[self.temp_sheet3_model.sheetname]
                if self.temp_sheet3_model.sheetname
                else None
            )
            temp_sheet4 = (
                self.template_workbook[self.temp_sheet4_model.sheetname]
                if self.temp_sheet4_model.sheetname
                else None
            )
            temp_sheet5 = (
                self.template_workbook[self.temp_sheet5_model.sheetname]
                if self.temp_sheet5_model.sheetname
                else None
            )

            temp_sheet6 = (
                self.template_workbook[self.temp_sheet6_model.sheetname]
                if self.temp_sheet6_model.sheetname
                else None
            )

        print("fill free OK!")

    def fill_sheet(
        self,
        org_sheet,
        org_start_row,
        org_end_row,
        org_code_col,
        org_fill_col,
        temp_sheet,
        temp_start_row,
        temp_end_row,
        temp_code_col,
        temp_fill_col,
    ):
        if temp_sheet is None:
            return
        for row in range(org_start_row, org_end_row + 1):
            org_code = org_sheet.cell(row=row, column=org_code_col).value
            if org_code is None or org_code == 0 or org_code == "":
                continue
            for tr in range(temp_start_row, temp_end_row + 1):
                temp_code = temp_sheet.cell(row=tr, column=temp_code_col).value
                if org_code == temp_code:
                    value = org_sheet.cell(row=row, column=org_fill_col).value
                    if value is None or value == 0:
                        continue
                    temp_sheet.cell(row=tr, column=temp_fill_col).value = value
        temp_sheet.delete_cols(temp_code_col)


class ExcelFillColumn:
    """操作 .xlsx 文件"""

    def fill_all_hz(
        self,
        src_filename=None,
        src_sheetname=None,
        src_client_id=None,
        src_code_col=None,
        src_start_row=None,
        src_end_row=None,
        src_start_col=None,
        src_end_col=None,
        dest_filename=None,
        dest_sheetname=None,
        dest_client_row=None,
        dest_code_col=None,
        dest_start_row=None,
        dest_end_row=None,
        dest_start_col=None,
        dest_end_col=None,
        is_sum=True,
    ):
        """客户和代码相等时，把源表相应的内容累加到目的表中"""
        if not dest_filename:
            dest_filename = src_filename
        swb = openpyxl.load_workbook(filename=src_filename, data_only=True)
        dwb = openpyxl.load_workbook(filename=dest_filename)
        src_sheet = swb[src_sheetname]
        dest_sheet = dwb[dest_sheetname]
        try:
            for r1 in range(src_start_row, src_end_row + 1):
                src_codevalue = src_sheet.cell(
                                row=r1, column=src_code_col
                            ).value
                if not src_codevalue: continue
                if src_end_col > 10:
                    print(f'正在录入第{r1}行, 征订代码: {src_codevalue} ...')
                for c1 in range(src_start_col, src_end_col + 1):
                    src_clientvalue = src_sheet.cell(
                                row=src_client_id, column=c1
                            ).value
                    if not src_clientvalue: continue
                    src_cell = src_sheet.cell(row=r1, column=c1)
                    if not src_cell or not src_cell.value:
                        continue
                    for r2 in range(dest_start_row, dest_end_row + 1):
                        dest_codevalue = dest_sheet.cell(row=r2, column=dest_code_col).value
                        if not dest_codevalue: continue
                        for c2 in range(dest_start_col, dest_end_col + 1):
                            dest_cell = dest_sheet.cell(row=r2, column=c2)                            
                            dest_clientvalue =  dest_sheet.cell(row=dest_client_row, column=c2).value
                            if not dest_clientvalue: continue 
                            src_clientvalue = str(src_clientvalue).strip()
                            dest_clientvalue = str(dest_clientvalue).strip()
                            src_codevalue = str(src_codevalue).strip()
                            dest_codevalue = str(dest_codevalue).strip()
                            if (
                                src_clientvalue == dest_clientvalue
                                and src_codevalue == dest_codevalue
                            ):
                                if is_sum:
                                    srcval = tryfloat(src_cell.value)
                                    if srcval == 0: continue
                                    if not dest_cell.value:
                                        dest_cell.value = 0
                                    dest_cell.value += srcval
                                else:
                                    dest_cell.value = src_cell.value  # 非数值不累加
        except:
            print("error: src={}, dest={}".format(src_cell.value, dest_cell.value))
            return
        dwb.save(dest_filename)
        print("save ok!")

    def fill_all_xt(
        self,
        src_filename=None,
        src_sheetname=None,
        src_client_id=None,
        src_code_col=None,
        src_start_row=None,
        src_end_row=None,
        src_start_col=None,
        src_end_col=None,
        dest_filename=None,
        dest_sheetname=None,
        dest_client_row=None,
        dest_code_col=None,
        dest_start_row=None,
        dest_end_row=None,
        dest_start_col=None,
        dest_end_col=None,
        is_sort = False
    ):
        """客户和代码相等时，把源表相应的内容累加到目的表中
        参数
            is_sort: 为提高效率, 按代码进行排序后再进行操作
        """

        # 注意: 源表结束列已忽略
        c1 = src_start_col

        if not dest_filename:
            dest_filename = src_filename
        swb = openpyxl.load_workbook(filename=src_filename.strip(), data_only=True)
        dwb = openpyxl.load_workbook(filename=dest_filename.strip())
        src_sheet = swb[src_sheetname.strip()]
        dest_sheet = dwb[dest_sheetname.strip()]
        try:
            line_count = src_start_row
            for r2 in range(dest_start_row, dest_end_row + 1):
                dest_code_cell = dest_sheet.cell(row=r2, column=dest_code_col)
                if not dest_code_cell or not dest_code_cell.value: continue
                print(f'正在录入: {dest_code_cell.value}...')
                if is_sort: 
                    srcRow = line_count
                    print(f'    正在从源表的 {line_count} 行填充...')
                else: srcRow = src_start_row
                for c2 in range(dest_start_col, dest_end_col + 1):
                    dest_client_cell = dest_sheet.cell(row=dest_client_row, column=c2)
                    if not dest_client_cell or not dest_client_cell.value: continue

                    dest_cell = dest_sheet.cell(row=r2, column=c2)
                    temp = 0
                    for r1 in range(srcRow, src_end_row + 1):
                        src_cell = src_sheet.cell(row=r1, column=c1)
                        if not src_cell or not src_cell.value:
                            continue
                        src_client_cell = src_sheet.cell(
                            row=r1, column=src_client_id
                        )
                        if not src_client_cell or not src_client_cell.value: continue
                        src_code_cell = src_sheet.cell(
                            row=r1, column=src_code_col
                        )

                        src_codevalue = str(src_code_cell.value).strip()
                        dest_codevalue = str(dest_code_cell.value).strip()
                        src_clientvalue = str(src_client_cell.value).strip()
                        dest_clientvalue = str(dest_client_cell.value).strip()
                        if (
                            src_clientvalue == dest_clientvalue
                            and src_codevalue == dest_codevalue
                        ):
                            temp += tryfloat(src_cell.value)
                            line_count += 1
                        if is_sort and src_codevalue != dest_codevalue: break
                    dest_cell.value = temp 
                    # print(dest_cell.value, end=', ')
        except:
            print(f"error: src_r1={r1}, dest_r2={r2},dest_c2={c2}")
            return
        dwb.save(dest_filename)
        print("save ok!")
   

    def fill_all_sum(
        self,
        dest_filename=None,
        dest_sheetname=None,
        dest_sum_row=None,
        dest_price_col=None,
        dest_start_row=None,
        dest_end_row=None,
        dest_start_col=None,
        dest_end_col=None,
    ):
        """客户和代码相等时，把源表相应的金额累加到目的表中"""
        dwb = openpyxl.load_workbook(filename=dest_filename, data_only=True)
        dest_sheet = dwb[dest_sheetname]
        try:
            for c in range(dest_start_col, dest_end_col + 1):
                for r in range(dest_start_row, dest_end_row + 1):
                    number_cell = dest_sheet.cell(row=r, column=c)
                    prict_cell = dest_sheet.cell(row=r, column=dest_price_col)
                    if number_cell is None or prict_cell is None:
                        continue
                    sum_cell = dest_sheet.cell(row=dest_sum_row, column=c)
                    if sum_cell.value is None:
                        sum_cell.value = 0
                    sum_cell.value += tryfloat(number_cell.value) * tryfloat(prict_cell.value)
                print(sum_cell.value)
        except:
            print("error: dest={}".format(number_cell.value))
            return
        dwb.save(dest_filename)
        print("save ok!")

    def fill_col(
        self,
        src_filename=None,
        src_sheetname=None,
        src_code_col=None,
        src_start_row=None,
        src_end_row=None,
        src_fill_col=None,
        dest_filename=None,
        dest_sheetname=None,
        dest_code_col=None,
        dest_start_row=None,
        dest_end_row=None,
        dest_fill_col=None,
        is_sum=False,
        is_sort = False
    ):
        """根据代码列, 填充源表相应列到目的表中
        参数
            is_sort: 遇到 10万 * 10万 的填充, 为提高效率, 添加排序, 源表少于目标表
        """
        if not dest_filename:
            dest_filename = src_filename
        swb = openpyxl.load_workbook(filename=src_filename, data_only=True)
        dwb = openpyxl.load_workbook(filename=dest_filename)
        src_sheet = swb[src_sheetname]
        dest_sheet = dwb[dest_sheetname]
        c1 = src_fill_col
        c2 = dest_fill_col
        # print('填充单列, 如数量过大且目标表没有重复项时, 建议排序后再操作...')
        for r1 in range(src_start_row, src_end_row + 1):
            src_cell = src_sheet.cell(row=r1, column=c1)
            if src_cell.value is None or src_cell.value == 0:
                continue
            if is_sort: 
                sortRow = r1
                if r1 % 2000 == 0: 
                    print(f'排序已填充源表 {r1} 行了...')
            else: sortRow = dest_start_row
            for r2 in range(sortRow, dest_end_row + 1):
                dest_cell = dest_sheet.cell(row=r2, column=c2)
                src_codevalue = src_sheet.cell(row=r1, column=src_code_col).value
                if src_codevalue is None: continue
                dest_codevalue = dest_sheet.cell(row=r2, column=dest_code_col).value
                src_codevalue = str(src_codevalue).strip()
                dest_codevalue = str(dest_codevalue).strip()
                if src_codevalue == dest_codevalue:
                    if dest_cell.value is None:
                        dest_cell.value = 0
                    if is_sum:
                        dest_cell.value = tryfloat(dest_cell.value)
                        dest_cell.value += tryfloat(src_cell.value)
                    else:
                        dest_cell.value = src_cell.value
                        if is_sort: break
        dwb.save(dest_filename)
        print("save ok!")


class ExcelAtuoInput:
    def __init__(
        self,
        filename,
        sheetname,
        client_row,
        code_col,
        start_row,
        end_row,
        start_col,
        end_col,
        code_number=None,
        decrease=False,
    ):
        """
        code_number: 教辅 =None , 教材 =6
        decrease: False - 添加;  True - 调减,需关闭输入法
        """
        self.filename = filename
        self.workbook = load_workbook(filename, data_only=True)
        self.sheetname = sheetname
        self.sheet = self.workbook[sheetname]

        self.client_row = client_row
        self.code_col = code_col
        self.start_row = start_row
        self.end_row = end_row
        self.start_col = start_col
        self.end_col = end_col
        self.code_number = code_number
        self.decrease = decrease

    def InputCols(self):
        """
        一户多码，一列一列的输入
        """
        for c in range(self.start_col, self.end_col + 1):
            client_name = self.sheet.cell(
                row=self.client_row + 1, column=c
            ).value
            clientId = self.sheet.cell(row=self.client_row, column=c).value
            if not client_name or not clientId:
                continue
            ok = auto.confirm(f"一户多码， 是否开始自动录入客户：{client_name}?")
            if ok != "OK":
                if "OK" == auto.confirm("是否退出？(OK 退出，Cancel 跳过)"):
                    break
                else:
                    continue
            for r in range(self.start_row, self.end_row + 1):
                v = self.sheet.cell(row=r, column=c).value
                v = tryint(v)
                code = self.sheet.cell(row=r, column=self.code_col).value
                if not v or not code:
                    continue
                if (self.decrease and v > 0) or (not self.decrease and v < 0):
                    continue
                if self.code_number is None:
                    auto.typewrite(str(code), interval=0.1)
                else:
                    auto.typewrite(str(code)[-self.code_number :], interval=0.1)
                auto.press("return")
                auto.typewrite(str(v), interval=0.1)
                auto.press("return")
                if self.decrease:
                    continue
                auto.press("return")
        auto.confirm("一户多码，录入完成！")

    def InputRows(self):
        """
        一码多户，一行一行的输入，教辅吴山客户编号不一致，不建议使用
        """
        for r in range(self.start_row, self.end_row + 1):
            code = self.sheet.cell(row=r, column=self.code_col).value
            if not code:
                continue
            ok = auto.confirm(f"一码多户，是否开始自动录入征订代码：{code}?")
            if ok != "OK":
                if "OK" == auto.confirm("是否退出？(OK 退出，Cancel 跳过)"):
                    break
                else:
                    continue
            for c in range(self.start_col, self.end_col + 1):
                v = self.sheet.cell(row=r, column=c).value
                v = tryint(v)
                client_id = self.sheet.cell(row=self.client_row, column=c).value
                client_name = self.sheet.cell(row=self.client_row+1, column=c).value
                if not client_id or not v or not client_name:
                    continue
                if (self.decrease and v > 0) or (not self.decrease and v < 0):
                    continue
                auto.typewrite(str(client_id), interval=0.1)
                auto.press("return")
                auto.typewrite(str(v), interval=0.1)
                auto.press("return")
                auto.press("return")
        auto.confirm("一码多户，录入完成！")

    def InputCols_Refund(self):
        """
        销退录入, 一户多码，一列一列的输入
        """
        for c in range(self.start_col, self.end_col + 1):
            client_name = self.sheet.cell(
                row=self.client_row + 1, column=c
            ).value
            clientId = self.sheet.cell(row=self.client_row, column=c).value
            if not client_name or not clientId:
                continue
            ok = auto.confirm(f"销退录入, 一户多码， 是否开始自动录入客户：{client_name}?")
            if ok != "OK":
                if "OK" == auto.confirm("是否退出？(OK 退出，Cancel 跳过)"):
                    break
                else:
                    continue
            for r in range(self.start_row, self.end_row + 1):
                v = self.sheet.cell(row=r, column=c).value
                if not v or v > 0: 
                    print('销退数值: {v} 必须是小于 0!')
                    continue
                v = -v
                code = self.sheet.cell(row=r, column=self.code_col).value
                if not code:
                    continue
                auto.typewrite(str(code), interval=0.1)
                auto.press('return')            
                time.sleep(1)
                auto.press("return")
                auto.typewrite(str(v), interval=0.1)
                auto.press("return")
                auto.press("return")
                auto.press("return")
                time.sleep(1)
                # if self.decrease:
                #     continue
                # auto.press("return")
        auto.confirm("一户多码，录入完成！")



class ExcelTask:
    """九丘生成分发明细专用, 格式固定
        c2: 活动名称
        c3: 学校名称
        c4: 年级
        c5: 班
        c6: 学生姓名
        c10: 数量
        c11: 片区
    """

    def __init__(
        self,
        src_path,
        src_sheetname,
        src_start_row,
        src_end_row,
        dest_path,
        dest_sheetname,
    ):
        self.src_workbook = load_workbook(src_path, data_only=True)
        self.src_sheet = self.src_workbook[src_sheetname]
        self.src_start_row = src_start_row
        self.src_end_row = src_end_row
        self.dest_workbook = load_workbook(dest_path)
        self.dest_sheet = self.dest_workbook[dest_sheetname]
        self.dest_path = dest_path
        self.dest_sheetname = dest_sheetname

    def GetSrcValues(self):
        values = []
        try:
            for v in list(self.src_sheet.values)[self.src_start_row - 1 : self.src_end_row]:
                if len(v) > 16: v = v[:16]
                values.append(TempRecord(*v))
        except:
            print("Error: GetSrcValues()")
        return values

    def GetNames(self):
        values = self.GetSrcValues()
        names = {}
        for v in values:
            if v.c1 is None or v.c1 == "":
                continue
            if names.get(v.c1) is None:
                names[v.c1] = [v]
            else:
                names[v.c1].append(v)
        return names

    def GetClients(self):
        ''' 获取全部客户名称的字典 '''
        values = self.GetSrcValues()
        clients = {}
        try:
            for v in values:
                if v.c3 is None or v.c3 == "":
                    continue
                if clients.get(v.c3) is None:
                    clients[v.c3] = [v]
                else:
                    clients[v.c3].append(v)
        except :
            print("Error: in GetClients()")
        return clients

    def GetPackages(self, values):
        '''九丘网生成分发明细专用,根据学校或班的值获取活动包名称'''
        packages = {}
        try:
            for v in values:
                if v.c2 is None or v.c2=="":
                    continue
                if packages.get(v.c2) is None:
                    packages[v.c2] = [v]
                else: 
                    packages[v.c2].append(v)
        except:
            print("Error: in GetGrades()")
        return packages

    def GetGrades(self, values):
        ''' 根据客户的值获取年级的字典 '''
        grades = {}
        try:
            for v in values:
                if v.c4 is None or v.c4=="":
                    continue
                if grades.get(v.c4) is None:
                    grades[v.c4] = [v]
                else: 
                    grades[v.c4].append(v)
        except:
            print("Error: in GetClasses()")
        return grades

    def GetClasses(self, values):
        '''根据年级的值获取班级的字典'''
        classes = {}
        try:
            for v in values:
                if v.c5 is None or v.c5=="":
                    continue
                if classes.get(v.c5) is None:
                    classes[v.c5] = [v]
                else: 
                    classes[v.c5].append(v)
        except:
            print("Error: in GetClasses()")
        return classes
            

    def FillTask(self):
        names = self.GetNames()
        for k, values in names.items():
            clients = []
            c4, c5, c6 = 0, 0, 0
            c7, c8, c9 = 0, 0, 0
            for v in values:
                if v.c2 != "小计":
                    clients.append(v.c2)
                c4 += tryfloat(v.c4)
                c5 += tryfloat(v.c5)
                c6 += tryfloat(v.c6)
                c7 += tryfloat(v.c7)
                c8 += tryfloat(v.c8)
                c9 += tryfloat(v.c9)
            clients = "、 ".join(clients)
            w = 10000
            c4 = round(c4 / w, 1)
            c5 = round(c5 / w, 1)
            c6 = round(c6 / w, 1)
            c7 = round(c7 / w, 1)
            c8 = round(c8 / w, 1)
            c9 = round(c9 / w, 1)
            self.dest_sheet.cell(row=2, column=2).value = k
            self.dest_sheet.cell(row=3, column=2).value = clients
            self.dest_sheet.cell(row=5, column=2).value = f"总任务:  {c9} 万元"
            self.dest_sheet.cell(
                row=6, column=2
            ).value = f"收费教材： {c6} 万元(其中课后服务新增任务：{c4} 万元)"
            self.dest_sheet.cell(
                row=7, column=2
            ).value = f"教    辅： {c7}  万元（其中幼儿资源包任务：{c5}  万元）"
            self.dest_sheet.cell(row=8, column=2).value = f"读书活动： {c8} 万元"
            outdir = os.path.join(os.path.dirname(self.dest_path), "output-责任书")
            if not os.path.exists(outdir):
                os.mkdir(outdir)
            self.dest_workbook.save(os.path.join(outdir, f"{k}任务责任书.xlsx"))
            self.dest_workbook = load_workbook(self.dest_path)
            self.dest_sheet = self.dest_workbook[self.dest_sheetname]

    def GenerateDetails(self):
        '''生成学校班级分发明细'''
        outdir = os.path.join(os.path.dirname(self.dest_path), 'out-分发明细')
        if not os.path.exists(outdir):
            os.mkdir(outdir)
        clients = self.GetClients()
        for client, client_values in clients.items():
            r2c2 = client
            r3c2 = ''
            area_name = client_values[0].c11
            print(f'正在生成{area_name}...')
            tempdir = os.path.join(outdir,area_name)
            if not os.path.exists(tempdir):
                os.mkdir(tempdir)
            packages = self.GetPackages(client_values)
            row=4
            for package, package_values in packages.items():
                count = len(package_values)
                if count == 0: continue
                r3c2 += f"{package} ({count}人),  "
            self.dest_sheet.cell(row=2,column=2).value = r2c2
            self.dest_sheet.cell(row=3,column=2).value = r3c2

            grades = self.GetGrades(client_values)
            for grade, grade_values in grades.items():
                classes = self.GetClasses(grade_values)
                for c, cv in classes.items():
                    packages2 = self.GetPackages(cv)
                    for pack2, pack2_values in packages2.items():
                        self.dest_sheet.cell(row=row, column=1).value = f" {grade} {c} ({len(pack2_values)}人): {pack2}  " #{cv.c4} 年级
                        names = ""
                        for v2 in pack2_values:
                            if v2.c6 is None: continue
                            names +=  v2.c6 + "、"
                        self.dest_sheet.cell(row=row, column=2).value = names.strip("、")
                        row += 1

            max_row = self.dest_sheet.max_row
            for row in range(max_row, 0, -1):
                if all([cell.value is None for cell in self.dest_sheet[row]]):
                    self.dest_sheet.delete_rows(row)

            self.dest_workbook.save(os.path.join(tempdir, f"{client}-分发明细.xlsx"))
            self.dest_workbook = load_workbook(self.dest_path)
            self.dest_sheet = self.dest_workbook[self.dest_sheetname]
        print('生成分发明细成功!')
                    
    def GenStudents2(self):
        ''' 导入学生,已废弃 '''
        names = self.GetNames()
        
        outdir = os.path.join(os.path.dirname(self.dest_path), "output-学生导入")
        if not os.path.exists(outdir):
            os.mkdir(outdir)
       
        for k, values in names.items():
            row = 2
            for v in values:
                self.dest_sheet.cell(row=row, column=1).value = v.c2
                self.dest_sheet.cell(row=row, column=2).value = v.c3
                self.dest_sheet.cell(row=row, column=3).value = v.c4
                row += 1
            self.dest_workbook.save(os.path.join(outdir, f"{k}-学生导入模板.xlsx"))
            self.dest_workbook = load_workbook(self.dest_path)
            self.dest_sheet = self.dest_workbook[self.dest_sheetname]



class R:
    @staticmethod
    def IsNumber(value):
        try:
            v = float(value)
        except:
            return False
        else:
            return True

    @staticmethod
    def FillAllColumns():
        """根据代码，客户ID， 汇总填充"""
        filename = r"C:\Users\Administrator\Desktop\12.31-地方教材报订.xlsx"
        # destname = r'C:\Users\Administrator\Desktop\12.2-义教教材录入.xlsx'
        destname = filename
        exl = ExcelFillColumn()
        exl.fill_all_hz(
            src_filename=filename,
            src_sheetname="1",
            src_client_id=4,
            src_code_col=5,
            src_start_row=6,
            src_end_row=37,
            src_start_col=6,
            src_end_col=62,
            dest_filename=destname,
            dest_sheetname="7.2",
            dest_client_row=3,
            dest_code_col=5,
            dest_start_row=6,
            dest_end_row=39,
            dest_start_col=7,
            dest_end_col=46,
        )
        print("fill all OK!")

    @staticmethod
    def FillColumn():
        """根据代码， 填充列"""
        src_filename = r"C:\Users\Administrator\Desktop\12.7-报订-22春环潭二小追加老师 收费 教本.xlsx"

        # dest_filename = src_filename
        dest_filename = r"C:\Users\Administrator\Desktop\11.22-教本教参录入.xlsx"

        exl = ExcelFillColumn()
        exl.fill_col(
            src_filename=src_filename,
            src_sheetname="2.01",
            src_code_col=5,
            src_start_row=1,
            src_end_row=300,
            src_fill_col=7,
            dest_filename=dest_filename,
            dest_sheetname="2.22",  # 1.2 教参， 2.22 教本
            dest_code_col=5,
            dest_start_row=1,
            dest_end_row=300,
            dest_fill_col=7,
            is_sum=False,
        )
        print("fill col OK!")

    @staticmethod
    def AutoInputCols():
        """一店多书， 自动录入"""
        a = ExcelAtuoInput(
            filename=r"C:\Users\Administrator\Desktop\2.16-曾都区教研室教参追加.xlsx",  # 录入文件路径
            sheetname="1.2",  # 表名
            client_row=3,  # 客户编码行
            code_col=5,  # 征订代码列
            start_row=6,  # 起始行
            end_row=150,  # 结束行
            start_col=7,  # 起始列
            end_col=7,  # 结束列
            code_number=6,  # 教辅：None， 教材：6
            decrease=False,
        )  # True: 调减需关闭输入法， False: 添加录入

        a.InputCols()

    @staticmethod
    def AutoInputRows():
        """一书多店，自动录入"""
        a = ExcelAtuoInput(
            filename=r"C:\Users\Administrator\Desktop\2.9-民族大团结免费报订.xlsx",
            sheetname="2.01",
            client_row=3,
            code_col=5,
            start_row=111,
            end_row=111,
            start_col=7,
            end_col=31,
            code_number=6,  # 教辅：None， 教材：6
            decrease=True,
        )  # True: 调减需关闭输入法
        a.InputRows()

    @staticmethod
    def GenerateFrees():
        """生成免费报表"""
        # currdir = os.path.dirname(__file__)
        org_file = os.path.join(CurrentDir, "data/free/2.17-22春免费教材.xlsx")
        temp_file = os.path.join(CurrentDir, "data/free/T.xlsx")

        exl_free = ExcelFree(temp_file)
        exl_free.open(org_file)
        exl_free.init_models(
            org_sheet1_name="2.01",
            org_sheet1_start_row=6,
            org_sheet1_end_row=159,
            org_sheet1_start_col=7,
            org_sheet1_end_col=31,
            org_sheet1_client_row=5,
            org_sheet1_code_col=5,
            org_sheet2_name="2.02",
            org_sheet2_start_row=6,
            org_sheet2_end_row=159,
            org_sheet2_start_col=7,
            org_sheet2_end_col=45,
            org_sheet2_client_row=5,
            org_sheet2_code_col=5,
            temp_sheet0_name="附件1",
            temp_sheet0_client_row=4,
            temp_sheet0_client_col=1,
            temp_sheet1_name="附件2",
            tmep_sheet1_code_col=7,
            temp_sheet1_start_row=7,
            temp_sheet1_end_row=103,
            temp_sheet1_fill_col=5,
            temp_sheet2_name="",
            tmep_sheet2_code_col=6,
            temp_sheet2_start_row=6,
            temp_sheet2_end_row=23,
            temp_sheet2_fill_col=4,
            temp_sheet3_name="",
            tmep_sheet3_code_col=6,
            temp_sheet3_start_row=7,
            temp_sheet3_end_row=8,
            temp_sheet3_fill_col=4,
            temp_sheet4_name="",
            tmep_sheet4_code_col=5,
            temp_sheet4_start_row=6,
            temp_sheet4_end_row=6,
            temp_sheet4_fill_col=4,
            temp_sheet5_name="",
            tmep_sheet5_code_col=6,
            temp_sheet5_start_row=7,
            temp_sheet5_end_row=9,
            temp_sheet5_fill_col=4,
            temp_sheet6_name="",
            tmep_sheet6_code_col=6,
            temp_sheet6_start_row=7,
            temp_sheet6_end_row=9,
            temp_sheet6_fill_col=4,
        )
        exl_free.generate_free()
        print("gen_free OK!")

    @staticmethod
    def FillCategory():
        """
        填充销售销退类别
        """
        fileanme = os.path.join(CurrentDir, r"data\account\减账.xlsx")
        sale = ExcelSale()
        sale.open(fileanme)

        sale.fill_category(
            x_sheetname="xs",
            x_category_col=9,
            org_sheetname="明细",
            org_client_row=3,
            org_date_col=2,
            org_category_col=1,
            org_start_row=6,
            org_end_row=500,
            org_start_col=3,
            org_end_col=100,
        )
        sale.fill_category(
            x_sheetname="xt",
            x_category_col=9,
            org_sheetname="明细",
            org_client_row=3,
            org_date_col=2,
            org_category_col=1,
            org_start_row=6,
            org_end_row=500,
            org_start_col=3,
            org_end_col=100,
            is_xt=True,
        )
        sale.save(fileanme)
        print("fill_category OK!")

    @staticmethod
    def GenerateSales():
        """生成销售确认函"""
        fileanme = os.path.join(CurrentDir, r"data\sale\12.21-4.24-22春教材教辅销售明细.xlsx")
        sale = ExcelSale()
        sale.open(fileanme)

        sale.gen_sales(
            "xs", "xt", os.path.join(CurrentDir, r"data/sale/T.xlsx")
        )  # , changed_catgory='教材教辅')    # changed_category=None 取消修改类别
        print("gen_sales OK!")

    @staticmethod
    def GenerateAccount():
        """生成做账明细"""
        org_path = os.path.join(CurrentDir, r"data\account\减账.xlsx")
        dest_path = os.path.join(CurrentDir, r"data\account\T.xlsx")
        exl: ExcelAccount = ExcelAccount(org_path=org_path, dest_path=dest_path)
        xs_config = ExcelConfig(
            sheet_name="xs",
            client_id=4,
            client_name=5,
            number=7,
            amount=8,
            category=9,
            start_row=3,
            end_row=3000,
        )
        xt_config = ExcelConfig(
            sheet_name="xt",
            client_id=4,
            client_name=5,
            number=7,
            amount=8,
            category=9,
            start_row=3,
            end_row=1200,
        )
        dest_config = ExcelConfig(
            sheet_name="账目", client_name=7, amount=5, number=10, start_row=3, category=3
        )
        exl.config(xs_config=xs_config, xt_config=xt_config, dest_config=dest_config)
        exl.fill()

    @staticmethod
    def GenerateTask():
        """
        生成任务责任书，格式有变，需改代码
        """
        src_path = r"E:\lab\pyexcel\data\task\1.12-21任务.xlsx"
        dest_path = r"E:\lab\pyexcel\data\task\1.11-责任书.xlsx"
        exl = ExcelTask(
            src_path=src_path,
            src_sheetname="1",
            src_start_row=5,
            src_end_row=101,
            dest_path=dest_path,
            dest_sheetname="2",
        )
        exl.FillTask()

        print("fill OK!")


if __name__ == "__main__":
    # R.AutoInputRows()       # 一书多店
    # R.AutoInputCols()  # 一店多书

    # R.FillColumn()        # 填充单列
    # R.FillAllColumns()      # 填充汇总

    # R.GenerateFrees()     # 生成免费申报
    # R.FillCategory()        # 填充销售销退类别
    # R.GenerateSales()     # 生成销售确认函
    # R.GenerateAccount()  # 生成做账明细
    # R.GenerateTask()        # 生成任务责任书

    # ExcelConfig.GenClients()  # 生成客户信息

    pass
